"""
Created on 2024-12-28
@author: zhangxiaoming@file: wechatRobot_v0.1.py
@description: 微信机器人
"""
from queue import Empty
import openpyxl
import os
import time
import threading
from wcferry import Wcf

import requests
import json
from pymongo import MongoClient
import re
from Tools.shaidan_func import shaidan_xifen
from Tools.daka import insert_daka_data_to_table
import pandas as pd
from Tools.enhanceExcel import beautify_excel
wcf = Wcf()
# 检测当前PC端微信登录状态
is_login = wcf.is_login()
# 获取当前PC端微信账号信息
print(wcf.get_user_info())
# {'wxid': 'wxid_xxxxx', 'name': '昵称', 'mobile': '手机号', 'home': ''}

wcf.enable_receiving_msg(pyq=True)
lock = threading.Lock()  # 创建一个全局锁对象
# 用于存储被@的人的消息和时间，以及是否已回复
pending_mentions = {}

client = MongoClient("mongodb://localhost:27017/")
agentdb = client.agent
agent = agentdb.agent
db = client["agent"]  # 数据库名称
collection = db["check_in_records"]  # 集合名称
MAX_REMINDERS = 3  # 最大提醒次数

user_data = {}
####九天智能体#####
def get_weather(msg):  # 移动办公达人
    print("msg:" + msg)
    try:
        for x in agent.find({"AppName": "中国移动套餐资费顾问"}):
            Token = x.get("Token")
            appId = x.get("appId")
            url = x.get("url")
            print(Token, appId, url)
    except Exception as e:
        print(e, 13323)
    try:
        print("来自智能体收到：{}".format(msg))
        AuthorizationMsg = "Bearer {}".format(Token)
        # 定义请求的URL和头部信息
        headers = {
            "Content-Type": "application/json",
            "Authorization": AuthorizationMsg
        }
        # 定义请求的数据
        data = {
            "model": "jiutian-lan",
            "prompt": msg,
            "temperature": 0.9,
            "top_p": 0.5,
            "history": [],
            "appId": appId,
            "stream": False
        }
        # 发送POST请求
        response = requests.post(url, headers=headers, data=json.dumps(data))
        # 将JSON字符串解析为Python字典
        parsed_data = json.loads(response.text)
        # 提取所需的文本
        desired_text = parsed_data['choices'][0]['text']
        # print(desired_text)
        return desired_text
    except Exception as e:
        print(e, 13350)


# 记录督办消息到Excel
def append_to_excel(content, sender_name, timestamp_str, timestamp_ymd):
    ##filename = os.path.join(directory, f"{timestamp_ymd}督办记录表.xlsx"),指定目录
    filename = f"督办xlsx文件\\{timestamp_ymd}_督办记录表.xlsx"
    if not os.path.exists(filename):
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        sheet['A1'] = '内容'
        sheet['B1'] = '督办人'
        sheet['C1'] = '时间'
    else:
        workbook = openpyxl.load_workbook(filename)
        sheet = workbook.active

    next_row = sheet.max_row + 1
    sheet.cell(row=next_row, column=1, value=content)
    sheet.cell(row=next_row, column=2, value=sender_name)
    sheet.cell(row=next_row, column=3, value=timestamp_str)
    workbook.save(filename)


# 检查是否在规定时间内回复“收到”
def check_mentions():
    # global TIMEOUT
    TIMEOUT = 60  # 1分钟
    while True:
        current_time_YMD = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        current_time = time.time()
        print(f"当前时间：{current_time_YMD}")
        for roomid, mentions in list(pending_mentions.items()):  # 遍历每个群聊中的@信息
            to_remove = []
            for mentioned_id, mention_info in list(mentions.items()):  # 遍历每个@信息
                mention_time, has_replied, has_been_reminded, mentioned_nickname, reminder_count = mention_info
                print(
                    f"检查 @ {mentioned_id} ({mentioned_nickname}) 的状态，时间戳：{mention_time}, 回复：{has_replied}, 已提醒：{has_been_reminded}, 提醒次数：{reminder_count}")

                # 如果用户已经回复"收到"，跳过提醒
                if has_replied:
                    to_remove.append(mentioned_id)
                    print(f"@{mentioned_nickname} 已经回复了 '收到'，跳过提醒")

                    continue
                    # 如果提醒次数已经超过最大提醒次数，跳过
                if reminder_count >= MAX_REMINDERS:
                    to_remove.append(mentioned_id)
                    print(f"@{mentioned_nickname} 已经超过最大提醒次数 ({MAX_REMINDERS} 次)，跳过提醒")
                    continue

                    # 超过超时（60秒）并且没有回复
                if current_time - mention_time > TIMEOUT:
                    # 超过超时时间，没有回复“收到”，自动提醒最初的@对象
                    at_msg = f"@{mentioned_nickname} \u2005 因为在{TIMEOUT}秒内未回复，请尽快确认消息，回复：收到"

                    # 发送提醒消息
                    wcf.send_text(at_msg, roomid, mentioned_id)
                    print(f"已提醒 @{mentioned_nickname}，因为未在 {TIMEOUT} 秒内回复 '收到'。")
                    TIMEOUT = TIMEOUT + 60  # 每提醒一次增加超时时间

                    # 更新提醒次数和状态
                    mentions[mentioned_id] = (
                        mention_time, has_replied, True, mentioned_nickname, reminder_count + 1)  # 增加提醒次数

            for k in to_remove:
                del mentions[k]  # 删除已经回复或超过最大提醒次数的@信息
                TIMEOUT = 60  # 重置超时时间

        # 每隔10秒检查一次
        time.sleep(10)


# 统计打卡人数，存储到ecxel表格
# def count_people_numbers(msg_content, timestamp_str, timestamp_ymd, nickName, roomid, sender):
#     print(f"msg_content: {msg_content}")
#     matchsa = re.search(r"应到(.+?)人", msg_content)  # 匹配应到人数
#     matchaa = re.search(r"实到(.+?)人", msg_content)  # 匹配实到人数
#     wnamep = msg_content.find("网格签到")  # 匹配网格名称
#     wname = ""
#     if wnamep != -1:
#         wname = msg_content[:wnamep].strip()
#         print(f"wname: {wname}")
#     resultsa = matchsa.group(1)
#     print(f"resultsa: {resultsa}")
#     resultaa = matchaa.group(1)
#     print(f"resultaa: {resultaa}")
#     filename = f"网格打卡xlsx文件\\{timestamp_ymd}_网格打卡人数统计表.xlsx"  # 根据日期创建不同的Excel文件
#     if not os.path.exists(filename):
#         workbook = openpyxl.Workbook()
#         sheet = workbook.active
#         sheet['A1'] = '网格名称'
#         sheet['B1'] = '应到人数'
#         sheet['C1'] = '实到人数'
#         sheet['D1'] = '打卡时间'
#         sheet['E1'] = '原始消息'
#     else:
#         workbook = openpyxl.load_workbook(filename)
#         sheet = workbook.active
#
#     next_row = sheet.max_row + 1
#     sheet.cell(row=next_row, column=1, value=wname[6:])
#     sheet.cell(row=next_row, column=2, value=resultsa)
#     sheet.cell(row=next_row, column=3, value=resultaa)
#     sheet.cell(row=next_row, column=4, value=timestamp_str)
#     sheet.cell(row=next_row, column=5, value=msg_content[6:])
#     workbook.save(filename)
#     wcf.send_text("@{} 我收到啦".format(nickName), roomid, sender)
#     print(f"数据已成功插入单元格中。")
# 统计打卡人数，存储到ecxel表格
def count_people_numbers(msg_content, timestamp_str, timestamp_ymd, nickName, roomid, sender):
    global user_data

    # 清理过期数据
    cleanup_expired_data()

    print(f"msg_content: {msg_content}")
    if_pic = '否'

    # 检查字典中是否存在该昵称
    if nickName in user_data:

        # 如果存在，更新打卡信息
        user_data[nickName]['txt'] += 1
        print(f"用户 {nickName} 的当前数据: {user_data[nickName]}")
    else:
        # 如果不存在，创建新的打卡信息
        user_data[nickName] = {"image": 0, "txt": 1, "timestamp": time.time()}
        print(f"昵称：{nickName}，打卡信息已创建")

    # 检查 image 和 txt 是否都大于零
    if user_data[nickName]['image'] > 0 and user_data[nickName]['txt'] > 0:
        print(f"昵称：{nickName}，image 和 txt 都大于零，打卡已录入")
        # wcf.send_text("打卡已录入", roomid, nickName)
        if_pic = '是'
        matchsa = re.search(r"应到(.+?)人", msg_content)  # 匹配应到人数
        matchaa = re.search(r"实到(.+?)人", msg_content)  # 匹配实到人数
        wnamep = msg_content.find("网格")  # 匹配网格名称
        wname = ""
        if wnamep != -1:
            wname = msg_content[:wnamep].strip()
            print(f"wname: {wname}")
        resultsa = matchsa.group(1)
        print(f"resultsa: {resultsa}")
        resultaa = matchaa.group(1)
        print(f"resultaa: {resultaa}")
        filename = f"网格打卡xlsx文件\\{timestamp_ymd}_网格打卡人数统计表.xlsx"  # 根据日期创建不同的Excel文件
        if not os.path.exists(filename):
            workbook = openpyxl.Workbook()
            sheet = workbook.active
            sheet['A1'] = '网格名称'
            sheet['B1'] = '应到人数'
            sheet['C1'] = '实到人数'
            sheet['D1'] = '打卡时间'
            sheet['E1'] = '原始消息'
            sheet['F1'] = '是否晒图'
        else:
            workbook = openpyxl.load_workbook(filename)
            sheet = workbook.active

        next_row = sheet.max_row + 1
        sheet.cell(row=next_row, column=1, value=wname.split(" ", 1)[1])
        sheet.cell(row=next_row, column=2, value=resultsa)
        sheet.cell(row=next_row, column=3, value=resultaa)
        sheet.cell(row=next_row, column=4, value=timestamp_str)
        sheet.cell(row=next_row, column=5, value=msg_content.split(" ", 1)[1])
        sheet.cell(row=next_row, column=6, value=if_pic)
        workbook.save(filename)
        wcf.send_text("@{} 打卡信息已录入".format(nickName), roomid, sender)
        print(f"数据已成功插入单元格中。")
        # print(f"昵称：{nickName}，等待两分钟后打卡已录入")
        # 删除该用户的打卡信息
        beautify_excel(filename, columns=["网格名称", "应到人数", "实到人数", "打卡时间", "原始消息", "是否晒图"])
        del user_data[nickName]

    if user_data[nickName]['image'] == 0 and user_data[nickName]['txt'] > 0:
        # 开始计时，等待两分钟
        user_data[nickName]['waiting'] = True
        user_data[nickName]['wait_start_time'] = time.time()
        user_data[nickName]['timestamp'] = time.time()
        wcf.send_text("请于两分钟内发送打卡图片", roomid, nickName)

        # 启动一个后台线程来处理等待逻辑

        threading.Thread(target=wait_and_send, args=(nickName, roomid, wcf, timestamp_ymd, sender)).start()
    else:
        # 更新时间戳
        user_data[nickName]['timestamp'] = time.time()



def wait_and_send(nickName, roomid, wcf, timestamp_ymd, sender):
    time.sleep(120)  # 等待两分钟
    if nickName in user_data and user_data[nickName].get('waiting', False):
        if_pic = '否'
        matchsa = re.search(r"应到(.+?)人", msg_content)  # 匹配应到人数
        matchaa = re.search(r"实到(.+?)人", msg_content)  # 匹配实到人数
        wnamep = msg_content.find("网格")  # 匹配网格名称
        wname = ""
        if wnamep != -1:
            wname = msg_content[:wnamep].strip()
            print(f"wname: {wname}")
        resultsa = matchsa.group(1)
        print(f"resultsa: {resultsa}")
        resultaa = matchaa.group(1)
        print(f"resultaa: {resultaa}")
        filename = f"网格打卡xlsx文件\\{timestamp_ymd}_网格打卡人数统计表.xlsx"  # 根据日期创建不同的Excel文件
        if not os.path.exists(filename):
            workbook = openpyxl.Workbook()
            sheet = workbook.active
            sheet['A1'] = '网格名称'
            sheet['B1'] = '应到人数'
            sheet['C1'] = '实到人数'
            sheet['D1'] = '打卡时间'
            sheet['E1'] = '原始消息'
            sheet['F1'] = '是否晒图'
        else:
            workbook = openpyxl.load_workbook(filename)
            sheet = workbook.active

        next_row = sheet.max_row + 1
        sheet.cell(row=next_row, column=1, value=wname.split(" ", 1)[1])
        sheet.cell(row=next_row, column=2, value=resultsa)
        sheet.cell(row=next_row, column=3, value=resultaa)
        sheet.cell(row=next_row, column=4, value=timestamp_str)
        sheet.cell(row=next_row, column=5, value=msg_content.split(" ", 1)[1])
        sheet.cell(row=next_row, column=6, value=if_pic)
        workbook.save(filename)
        wcf.send_text("@{} 等待两分钟后已将打卡信息录入".format(nickName), roomid, sender)
        print(f"数据已成功插入单元格中。")
        print(f"昵称：{nickName}，等待两分钟后打卡已录入")
        beautify_excel(filename, columns=["网格名称", "应到人数", "实到人数", "打卡时间", "原始消息", "是否晒图"])
        del user_data[nickName]  # 删除用户信息

def save_image_message(wcf, msg,timestamp_ymd, roomid, sender):
    global user_data

    # 清理过期数据
    cleanup_expired_data()

    try:
        # 获取当前时间并格式化
        timestamp_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        # 将时间中的空格和冒号替换为下划线
        formatted_time = timestamp_str.replace(" ", "_").replace(":", "_")

        # 打印消息信息
        print(f"收到图片消息：{msg.id}")
        print(f"图片消息内容：{msg.content}")
        print(f"图片ID消息：{msg.extra}")

        # 获取发送者的群昵称和发送时间
        group_members = wcf.get_chatroom_members(msg.roomid)
        nickName = group_members[msg.sender]
        # 将群昵称中的空格替换为下划线
        sender_nickname = nickName.replace(" ", "_")
        print(f"发送者昵称：{sender_nickname}")

        # 检查字典中是否存在该昵称
        if nickName in user_data:
            # 如果存在，更新打卡信息
            user_data[nickName]['image'] += 1

            # 检查 image 和 txt 是否都大于零
            if user_data[nickName]['image'] > 0 and user_data[nickName]['txt'] > 0:
                print(f"昵称：{nickName}，image 和 txt 都大于零，打卡已录入")
                if_pic = '是'
                matchsa = re.search(r"应到(.+?)人", msg_content)  # 匹配应到人数
                matchaa = re.search(r"实到(.+?)人", msg_content)  # 匹配实到人数
                wnamep = msg_content.find("网格")  # 匹配网格名称
                wname = ""
                if wnamep != -1:
                    wname = msg_content[:wnamep].strip()
                    print(f"wname: {wname}")
                resultsa = matchsa.group(1)
                print(f"resultsa: {resultsa}")
                resultaa = matchaa.group(1)
                print(f"resultaa: {resultaa}")
                filename = f"网格打卡xlsx文件\\{timestamp_ymd}_网格打卡人数统计表.xlsx"  # 根据日期创建不同的Excel文件
                if not os.path.exists(filename):
                    workbook = openpyxl.Workbook()
                    sheet = workbook.active
                    sheet['A1'] = '网格名称'
                    sheet['B1'] = '应到人数'
                    sheet['C1'] = '实到人数'
                    sheet['D1'] = '打卡时间'
                    sheet['E1'] = '原始消息'
                    sheet['F1'] = '是否晒图'
                else:
                    workbook = openpyxl.load_workbook(filename)
                    sheet = workbook.active

                next_row = sheet.max_row + 1
                sheet.cell(row=next_row, column=1, value=wname.split(" ", 1)[1])
                sheet.cell(row=next_row, column=2, value=resultsa)
                sheet.cell(row=next_row, column=3, value=resultaa)
                sheet.cell(row=next_row, column=4, value=timestamp_str)
                sheet.cell(row=next_row, column=5, value=msg_content.split(" ", 1)[1])
                sheet.cell(row=next_row, column=6, value=if_pic)
                workbook.save(filename)
                wcf.send_text("@{} 打卡消息已录入".format(nickName), roomid, sender)
                print(f"数据已成功插入单元格中。")
                beautify_excel(filename, columns=["网格名称", "应到人数", "实到人数", "打卡时间", "原始消息", "是否晒图"])
                # 删除该用户的打卡信息
                del user_data[nickName]
            else:
                # 更新时间戳
                user_data[nickName]['timestamp'] = time.time()
        else:
            # 如果不存在，创建新的打卡信息
            user_data[nickName] = {"image": 1, "txt": 0, "timestamp": time.time()}
            print(f"昵称：{nickName}，打卡信息已创建")
    # except Exception as e:
    #     print(f"处理图片消息时发生错误：{e}")

        # 图片文件名：发送时间 + 发送者昵称 + "打卡照片.jpg"
        # image_filename = f"{formatted_time}_{sender_nickname}_打卡照片.jpg"

        # # 下载图片
        # result_path = wcf.download_image(msg.id, msg.extra, image_path, timeout=60000)
        #
        # # 检查返回结果并重命名保存的文件
        # if result_path:
        #     # 使用新命名保存图片
        #     new_path = os.path.join(image_path, image_filename)
        #     os.rename(result_path, new_path)  # 重命名图片
        #
        #     print(f"图片成功保存到: {new_path}")
        # else:
        #     print("下载图片失败，请查看日志获取详细信息。")

    except Exception as e:
        print(f"发生错误: {e}")

def cleanup_expired_data():
    global user_data
    current_time = time.time()
    expired_keys = [nick for nick, data in user_data.items() if current_time - data['timestamp'] > 180]
    for nick in expired_keys:
        del user_data[nick]
        print(f"清理过期数据：{nick}")
# 处理消息



# 统计晒单数不细分业务类型，存储到ecxel表格
def count_show_order_numbers(msg, timestamp_str, timestamp_ymd, nickName, roomid, sender):
    msg_sd = {}
    msg_list = msg.split('\n')
    msg_sd['战队'] = msg_list[0]
    if ':' in msg_list[1]:
        msg_sd['业务'] = msg_list[1].split(':')[1]
    elif '：' in msg_list[1]:
        msg_sd['业务'] = msg_list[1].split('：')[1]
    if ':' in msg_list[2]:
        msg_sd['号码'] = msg_list[2].split(':')[1]
    elif '：' in msg_list[2]:
        msg_sd['号码'] = msg_list[2].split('：')[1]
    if ':' in msg_list[3]:
        msg_sd['发展人'] = msg_list[3].split(':')[1]
    elif '：' in msg_list[3]:
        msg_sd['发展人'] = msg_list[3].split('：')[1]
    filename = f"晒单统计xlsx文件\\{timestamp_ymd}_晒单统计表.xlsx"  # 根据日期创建不同的Excel文件
    if not os.path.exists(filename):
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        sheet['A1'] = '战队名称'
        sheet['B1'] = '业务名称'
        sheet['C1'] = '客户号码'
        sheet['D1'] = '发展人'
        sheet['E1'] = '晒单时间'
        sheet['F1'] = '原始消息'
    else:
        workbook = openpyxl.load_workbook(filename)
        sheet = workbook.active

    next_row = sheet.max_row + 1
    sheet.cell(row=next_row, column=1, value=msg_sd['战队'][7:])
    sheet.cell(row=next_row, column=2, value=msg_sd['业务'])
    sheet.cell(row=next_row, column=3, value=msg_sd['号码'])
    sheet.cell(row=next_row, column=4, value=msg_sd['发展人'])
    sheet.cell(row=next_row, column=5, value=timestamp_str)
    sheet.cell(row=next_row, column=6, value=msg[9:])
    workbook.save(filename)
    wcf.send_text("@{} 我收到啦".format(nickName), roomid, sender)
    print(f"数据已成功插入单元格中。")


# 统计晒单数，细分业务类型，存储到ecxel表格
# def xf_count_show_order_numbers(msg, timestamp_str, timestamp_ymd, nickName, roomid, sender):
#     msg_sd = {}
#     msg_list = msg.split('\n')
#     msg_sd['战队'] = msg_list[0]
#     if ':' in msg_list[1]:
#         msg_sd['业务'] = msg_list[1].split(':')[1]
#     elif '：' in msg_list[1]:
#         msg_sd['业务'] = msg_list[1].split('：')[1]
#     if ':' in msg_list[2]:
#         msg_sd['号码'] = msg_list[2].split(':')[1]
#     elif '：' in msg_list[2]:
#         msg_sd['号码'] = msg_list[2].split('：')[1]
#     if ':' in msg_list[3]:
#         msg_sd['发展人'] = msg_list[3].split(':')[1]
#     elif '：' in msg_list[3]:
#         msg_sd['发展人'] = msg_list[3].split('：')[1]
#
#     # 创建文件名，保存为当天日期的文件
#     filename = f"晒单统计xlsx文件\\{timestamp_ymd}_晒单统计表.xlsx"
#
#     # 如果文件不存在，则新建一个Excel文件
#     if not os.path.exists(filename):
#         workbook = openpyxl.Workbook()
#         sheet = workbook.active
#         sheet['A1'] = '战队名称'
#         sheet['B1'] = '业务名称'
#         sheet['C1'] = '客户号码'
#         sheet['D1'] = '发展人'
#         sheet['E1'] = '晒单时间'
#         sheet['F1'] = '宽带'
#         sheet['G1'] = '机顶盒'
#         sheet['H1'] = '视频彩铃'
#         sheet['I1'] = '移动APP'
#         sheet['J1'] = '10分满意'
#         sheet['K1'] = '和彩云'
#         sheet['L1'] = '完美一单'
#     else:
#         workbook = openpyxl.load_workbook(filename)
#         sheet = workbook.active
#
#     # 在Excel表格中找到下一个空行
#     next_row = sheet.max_row + 1
#
#     # 插入基础信息到表格中
#     sheet.cell(row=next_row, column=1, value=msg_sd['战队'][9:])
#     sheet.cell(row=next_row, column=2, value=msg_sd['业务'])
#     sheet.cell(row=next_row, column=3, value=msg_sd['号码'])
#     sheet.cell(row=next_row, column=4, value=msg_sd['发展人'])
#     sheet.cell(row=next_row, column=5, value=timestamp_str)
#
#     # 检查msg中是否包含指定字样，并填充相应的列
#     keywords = {
#         '宽带': 'F',
#         '机顶盒': 'G',
#         '视频彩铃': 'H',
#         '移动APP': 'I',
#         '10分满意': 'J',
#         '和彩云': 'K',
#         '完美一单': 'L'
#     }
#
#     # 对每个关键词进行检查并填充对应列
#     for keyword, column in keywords.items():
#         if keyword in msg:
#             sheet[f'{column}{next_row}'] = '已办理'
#
#     # 保存文件
#     workbook.save(filename)
#
#     # 发送确认消息
#     wcf.send_text("@{} 我收到啦".format(nickName), roomid, sender)
#     print(f"数据已成功插入单元格中。")
def xf_count_show_order_numbers(msg, timestamp_ymd, nickName, roomid, sender):
    # 定义表头
    columns = [
        "队伍名称",
        "发展业务",
        "客户号码",
        "发展人",
        "新入网",
        "宽带",
        "终端",
        "触点",
        "金虎开户",
        "是否完美一单",
        "开单时间",
        "原始消息",
    ]

    # 假设shaidan_xifen是自定义函数，返回msg的处理结果，确保其返回字典
    msg_sd = shaidan_xifen(msg=msg)

    # 判断业务场景
    yw = "、".join(msg_sd["业务"])  # 业务名用顿号连接
    row = [msg_sd["战队"], yw, msg_sd["号码"], msg_sd["发展人"]]

    # 根据业务检查是否已办理，并追加列
    for ywid in ["新入网", "宽带", "终端", "触点", "金虎开户"]:
        if ywid in msg_sd["业务"]:
            row.append("已办理")
        else:
            row.append("")

    row.append(msg_sd["是否完美一单"])
    row.append(msg_sd["时间"])
    row.append(msg_sd["原信息"])

    # 文件路径
    file_path = f"晒单统计xlsx文件\\{timestamp_ymd}_晒单统计表.xlsx"

    # 判断是否存在表格文件，如果不存在，创建文件
    if not os.path.exists(file_path):
        df = pd.DataFrame(columns=columns)  # 创建一个空的DataFrame作为表头
    else:
        try:
            df = pd.read_excel(file_path)  # 读取现有的Excel文件
        except Exception as e:
            print(f"读取失败: {e}")
            return

    # 如果原始消息已经存在，则不插入
    if msg_sd["原信息"] in df["原始消息"].values:
        print("已存在该条数据")
    else:
        # 将新行添加到DataFrame
        new_row = pd.DataFrame([row], columns=columns)
        df = pd.concat([df, new_row], ignore_index=True)

        # 保存Excel文件
        try:
            with pd.ExcelWriter(file_path, engine="xlsxwriter") as writer:
                df.to_excel(writer, index=False, sheet_name="当日统计")

                # 获取workbook和worksheet对象
                workbook = writer.book
                worksheet = writer.sheets["当日统计"]

                # 设置列宽和对齐方式
                header_format = workbook.add_format({
                    "align": "center",
                    "valign": "vcenter",
                    "bg_color": "#DCDCDC",
                    "bold": True,
                    "font_size": 12,
                    "border": 1,
                    "border_color": "#000000",
                })

                # 手动写入列名并应用格式
                for col_num, col_name in enumerate(df.columns.values):
                    worksheet.write(0, col_num, col_name, header_format)  # 列名从第1列开始

                # 设置列宽和对齐方式
                format_align = workbook.add_format({"align": "center", "valign": "vcenter"})
                widths = [20, 80, 10, 10, 10, 10, 10, 10, 10, 20, 15, 100]
                for col, width in zip(columns, widths):
                    col_idx = columns.index(col)
                    worksheet.set_column(col_idx, col_idx, width, format_align)

            print(f"已保存: {file_path}")
            wcf.send_text("@{} 我收到啦".format(nickName), roomid, sender)

        except Exception as e:
            print(f"保存失败: {e}")


# 统计打卡人数存储到mongodb数据库
def countPeopleNumbers_to_db(msg_content, timestamp_str, timestamp_ymd, nickName, roomid, sender):
    print(f"msg_content: {msg_content}")

    # 提取应到人数和实到人数
    matchsa = re.search(r"应到(.+?)人", msg_content)  # 匹配应到人数
    matchaa = re.search(r"实到(.+?)人", msg_content)  # 匹配实到人数

    if not matchsa or not matchaa:
        print("未能从消息中提取到应到或实到人数")
        return

    resultsa = matchsa.group(1)
    resultaa = matchaa.group(1)
    print(f"应到人数: {resultsa}, 实到人数: {resultaa}")

    # 提取网格名称
    wnamep = msg_content.find("网格签到")
    wname = ""
    if wnamep != -1:
        wname = msg_content[:wnamep].strip()
        print(f"网格名称: {wname}")

    # 准备存储到 MongoDB 的数据
    check_in_record = {
        "打卡网格名称": wname[6:],  # 可能需要根据实际情况调整索引
        "应到打卡人数": resultsa,
        "实到打卡人数": resultaa,
        "打卡时间": timestamp_str,
        "打卡原始消息": msg_content[6:],  # 存储原始消息
        "发消息的用户": nickName,  # 发消息的用户
        "标签日期": timestamp_ymd,  # 发消息的用户
    }

    # 插入数据到 MongoDB
    collection.insert_one(check_in_record)
    print(f"数据已成功插入 MongoDB: {check_in_record}")

    # 发送确认消息
    wcf.send_text("@{} 我收到啦".format(nickName), roomid, sender)


#  从数据库查询打卡信息，发送给用户
def fetch_check_in_records(msg_content, roomid, sender, timestamp_str_ymd):
    # 查询条件：根据打卡时间查找
    query = {"标签日期": timestamp_str_ymd}

    # 从 MongoDB 获取记录
    records = collection.find(query)

    if collection.count_documents(query) == 0:
        print("没有找到符合条件的记录。")
        return
    # 创建一个列表用于存储所有的消息
    messages = []
    # 格式化输出每条记录
    for record in records:
        grid_name = record.get("打卡网格名称", "未知网格")
        expected_people = record.get("应到打卡人数", "未知")
        actual_people = record.get("实到打卡人数", "未知")
        check_in_time = record.get("打卡时间", "未知时间")

        # 打印或返回格式化的消息
        message = f"{grid_name}网格应到{expected_people}人，实到 {actual_people}人。在 {check_in_time} 打卡成功。"
        messages.append(message)
    # 将所有消息合并为一个字符串，每条消息之间用换行符分隔
    all_messages = "\n".join(messages)
    wcf.send_text(all_messages, roomid, sender)  # 发送格式化的消息
    print(all_messages)


# 发送excel签到统计表
def send_signin_statistics(msg_content, roomid, sender, timestamp_str_ymd):
    filename = f"网格打卡xlsx文件\\{timestamp_str_ymd}_网格打卡人数统计表.xlsx"
    # 获取当前文件的绝对路径
    current_file_path = os.path.abspath(__file__)
    # 获取当前文件所在的目录路径（即项目根目录，假设当前文件在根目录下）
    project_root_dir = os.path.dirname(current_file_path)
    # 拼接成完整的文件路径
    excel_file_path = os.path.join(project_root_dir, filename)

    print(excel_file_path)

    # 使用锁来确保同时只有一个线程可以执行下面的代码块
    with lock:
        wcf.send_image(excel_file_path, roomid)


# 发送excel晒单统计表
def send_order_numbers(roomid, timestamp_str_ymd):
    filename = f"晒单统计xlsx文件\\{timestamp_str_ymd}_晒单统计表.xlsx"
    # 获取当前文件的绝对路径
    current_file_path = os.path.abspath(__file__)
    # 获取当前文件所在的目录路径（即项目根目录，假设当前文件在根目录下）
    project_root_dir = os.path.dirname(current_file_path)
    # 拼接成完整的文件路径
    excel_file_path = os.path.join(project_root_dir, filename)

    print(excel_file_path)

    # 使用锁来确保同时只有一个线程可以执行下面的代码块
    with lock:
        wcf.send_image(excel_file_path, roomid)


# def save_image_message(wcf, msg, image_path="D:\\yidonbangon\\Digitalrobot\\2024-12\\images"):
#     try:
#         # 获取当前时间并格式化
#         timestamp_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
#         # 将时间中的空格和冒号替换为下划线
#         formatted_time = timestamp_str.replace(" ", "_").replace(":", "_")
#
#         # 打印消息信息
#         print(f"收到图片消息：{msg.id}")
#         print(f"图片消息内容：{msg.content}")
#         print(f"图片消息ID：{msg.extra}")
#
#         # 获取发送者的群昵称和发送时间
#         group_members = wcf.get_chatroom_members(msg.roomid)
#         nickName = group_members[msg.sender]
#         # 将群昵称中的空格替换为下划线
#         sender_nickname = nickName.replace(" ", "_")
#         print(f"发送者昵称：{sender_nickname}")
#
#         # 图片文件名：发送时间 + 发送者昵称 + "打卡照片.jpg"
#         image_filename = f"{formatted_time}_{sender_nickname}_打卡照片.jpg"
#
#         # 下载图片
#         result_path = wcf.download_image(msg.id, msg.extra, image_path, timeout=60000)
#
#         # 检查返回结果并重命名保存的文件
#         if result_path:
#             # 使用新命名保存图片
#             new_path = os.path.join(image_path, image_filename)
#             os.rename(result_path, new_path)  # 重命名图片
#
#             print(f"图片成功保存到: {new_path}")
#         else:
#             print("下载图片失败，请查看日志获取详细信息。")
#
#     except Exception as e:
#         print(f"发生错误: {e}")


# 定义查询路径和文件夹
folders = {
    "晒单统计": "D:\\yidonbangon\\Digitalrobot\\2024-12\\晒单统计xlsx文件",
    "督办记录": "D:\\yidonbangon\\Digitalrobot\\2024-12\\督办xlsx文件",
    "网格打卡人数统计": "D:\\yidonbangon\\Digitalrobot\\2024-12\\网格打卡xlsx文件"
}


def send_history_table(msg, roomid):
    # 提取日期和关键字
    date_pattern = r"\d{4}-\d{2}-\d{2}"  # 日期格式：yyyy-mm-dd
    date_match = re.search(date_pattern, msg)

    if date_match:
        query_date = date_match.group()  # 获取消息中的日期
    else:
        print("没有找到有效的日期")

    # 提取关键字
    keywords = ["晒单统计", "督办记录", "网格打卡人数统计"]
    matched_keyword = None
    for keyword in keywords:
        if keyword in msg:
            matched_keyword = keyword
            break

    if not matched_keyword:
        print("没有找到有效的关键字")

    # 根据关键字和日期构建文件名
    file_name = f"{query_date}_{matched_keyword}表.xlsx"

    # 查找对应的文件夹
    folder_path = folders.get(matched_keyword)
    if not folder_path:
        print(f"没有找到对应的文件夹: {matched_keyword}")

    # 查找文件
    file_path = os.path.join(folder_path, file_name)

    if os.path.exists(file_path):
        # 文件存在，发送文件
        wcf.send_image(file_path, roomid)
        print(f"已发送文件: {file_path}")
    else:
        # 文件不存在
        print(f"没有找到该文件")


def help_msg(roomid):
    # 提示信息
    help_text = """
市场Bot 使用指南
  1.签到/打卡: 发送:@市场Bot 网格签到 或 @市场Bot 网格打卡
    例:@市场Bot 东南新城网格打卡，应到10人实到10人。
    
  2.晒单: 发送:@市场Bot 战队晒单
      例:@市场Bot 姚安直销战队晒单
      完美一单: 新入网+宽带+机顶盒+视频彩铃+移动APP+10分满意+和彩云
    客户号码:8089
    发展人:毛丽梅
    
  3.获取当天统计数据:
    * 签到/打卡统计:发送: @市场Bot 签到统计表/打卡统计表
    * 晒单统计:发送: @市场Bot 晒单统计表
    
  4.获取历史统计数据 (格式:（年）YYYY-（月）MM-（日）DD):
  
    *晒单统计:发送 @市场Bot YYYY-MM-DD 晒单统计 例:@市场Bot 2025-02-06 晒单统计
    
    *打卡统计:发送 @市场Bot YYYY-MM-DD 网格打卡人数统计 例:@市场Bot 2025-02-06 网格打卡人数统计
    
    如有疑问，随时联系！
    
****注意 @机器人必须手动@ 切勿直接发送文本****
    """
    # 格式化并返回提示信息
    wcf.send_text(help_text, roomid)


# 启动定时检查线程
def start_checking():
    check_thread = threading.Thread(target=check_mentions)
    check_thread.daemon = True  # 使线程在主程序结束时自动退出
    check_thread.start()


# 处理消息
target_roomid = '43321536618@chatroom'  # 替换为目标群聊ID

# 启动定时检查
start_checking()

while wcf.is_receiving_msg():
    try:
        msg = wcf.get_msg()
        print(msg)
        if msg.from_group():
            if msg.roomid == target_roomid:  # 确保消息来自目标群聊
                print(f"收到群聊消息：{msg.content}")
                content = msg.content.strip()  # 去除消息内容两端的空格
                # 获取当前时间（年月日 时:分:秒 格式）
                timestamp_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())

                if msg.type == 3:  # 只要是在指定的群里有图片新消息，都将图片保存下来
                    timestamp_str_ymd = time.strftime('%Y-%m-%d', time.localtime())
                    save_image_message(wcf, msg, timestamp_str_ymd, msg.roomid, msg.sender)

                if msg.is_at(wcf.get_self_wxid()):  # 如果微信机器人被@了
                    msg_content = msg.content  # 获取消息内容
                    date_pattern = re.compile(r"\d{4}-\d{2}-\d{2}")
                    if date_pattern.search(msg_content):
                        # 如果找到日期格式，则执行发送历史表格的逻辑
                        send_history_table(msg_content, msg.roomid)
                    elif "网格签到" in msg_content or "网格打卡" in msg_content:
                        group_members = wcf.get_chatroom_members(msg.roomid)
                        nickName = group_members[msg.sender]
                        timestamp_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                        timestamp_str_ymd = time.strftime('%Y-%m-%d', time.localtime())
                        # 根据需求替换函数
                        count_people_numbers(msg_content, timestamp_str, timestamp_str_ymd, nickName, msg.roomid,
                                             msg.sender)
                        # if insert_daka_data_to_table(msg_content.split(" ", 1)[1], nickName, False):
                        #     wcf.send_text("@{} 我收到啦".format(nickName), msg.roomid, msg.sender)
                        # else:
                        #     wcf.send_text("@{} 一小时内已打卡。".format(nickName), msg.roomid, msg.sender)

                    elif "签到统计表" in msg_content or "打卡统计表" in msg_content:
                        timestamp_str_ymd = time.strftime('%Y-%m-%d', time.localtime())  # 获取当前日期
                        # 根据需求替换函数
                        send_signin_statistics(msg_content, msg.roomid, msg.sender, timestamp_str_ymd)
                    elif "晒单统计表" in msg_content:
                        # 只执行“晒单统计表”的逻辑
                        timestamp_str_ymd = time.strftime('%Y-%m-%d', time.localtime())  # 获取当前日期
                        send_order_numbers(msg.roomid, timestamp_str_ymd)
                    elif "晒单" in msg_content:
                        # 执行“晒单”的逻辑
                        group_members = wcf.get_chatroom_members(msg.roomid)
                        nickName = group_members[msg.sender]
                        timestamp_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                        timestamp_str_ymd = time.strftime('%Y-%m-%d', time.localtime())  # 获取当前日期
                        count_show_order_numbers(msg_content, timestamp_str, timestamp_str_ymd, nickName, msg.roomid,
                                                 msg.sender)
                        # xf_count_show_order_numbers(msg_content[6:], timestamp_str_ymd, nickName, msg.roomid, msg.sender)
                    elif "help" in msg_content:
                        help_msg(msg.roomid)

                    # keywords = ["5G资费套餐介绍", "4G资费套餐介绍", "超出资费套餐介绍", "160元以上套餐介绍"]
                    # if any(keyword in msg_content for keyword in keywords):
                    #     group_members = wcf.get_chatroom_members(msg.roomid)
                    #     nickName = group_members[msg.sender]
                    #     wcf.send_text("@{} 我收到啦".format(nickName), msg.roomid, msg.sender)
                    #     sender_name = wcf.get_alias_in_chatroom(msg.sender, msg.roomid)  # 获取发送者的昵称
                    #     # 注意：这里假设去掉前6个字符是有特定意义的，否则请检查这个逻辑是否正确
                    #     # 如果不是每个关键词都需要去掉前6个字符，你可能需要在这里添加额外的逻辑来处理不同的情况
                    #     msg_content_processed = msg_content[6:]
                    #     weather_text = get_weather(msg_content_processed)
                    #     print(weather_text)
                    #     wcf.send_text("@{} {}".format(sender_name, weather_text), msg.roomid, msg.sender)

                    # 如果消息有" 督办 "关键字
                #     elif "督办记录" not in msg_content and "督办" in msg_content:
                #         timestamp_str_ymd = time.strftime('%Y-%m-%d', time.localtime())
                #         content_after_space = msg_content.split()[-1]  # 获取被督办的内容
                #         sender_name = wcf.get_alias_in_chatroom(msg.sender, msg.roomid)  # 获取发送者的昵称
                #         # 保存内容到Excel
                #         append_to_excel(content_after_space, sender_name, timestamp_str,
                #                         timestamp_str_ymd)  # 调用函数保存内容到Excel
                #         at_msg = f"@{sender_name} \u2005 消息已经记录"  # 发送回复消息
                #         wcf.send_text(at_msg, msg.roomid, msg.sender)  # 发送回复消息
                #
                #         # 如果消息中包含@
                #         if '@' in content:
                #             # 处理全角空格为普通空格
                #             content = msg_content[2:].strip()  # 确保每次都能找到被@的人的wxid，因为消息里有两个@符号，需要去掉一个
                #             content = content.replace(' ', ' ')  # 替换全角空格为普通空格
                #             mentioned_nickname = content.split('@')[1].split(' ')[0].strip()  # 提取@后面的昵称
                #
                #             # 使用昵称查找被@的人的wxid
                #             mentioned_id = None
                #             members = wcf.get_chatroom_members(msg.roomid)  # 获取群成员列表
                #             print(f"群成员信息：{members}")  # 输出群成员信息以便调试
                #
                #             # 确保返回的是字典类型
                #             if isinstance(members, dict):
                #                 for wxid, nickname in members.items():
                #                     if nickname == mentioned_nickname:
                #                         mentioned_id = wxid  # 获取该成员的 wxid
                #                         break
                #             else:
                #                 print("获取群成员信息失败，返回的不是一个字典")
                #
                #             if mentioned_id:
                #                 reminder_count = 0  # 默认提醒次数为0
                #                 # 记录该被@人的微信ID和当前时间，初始化其为未回复，未提醒，并且记录昵称
                #                 if msg.roomid not in pending_mentions:
                #                     pending_mentions[msg.roomid] = {}
                #
                #                 pending_mentions[msg.roomid][mentioned_id] = (
                #                     time.time(), False, False, mentioned_nickname, reminder_count)
                #                 print(f"已记录 @ {mentioned_nickname} 的微信ID {mentioned_id} 被提及，时间戳：{time.time()}")
                #
                #             else:
                #                 print(f"未找到被@人的微信ID，昵称: {mentioned_nickname}，可能在群聊中不存在")
                # # 如果消息是"收到"，检查是否是被@的人回复
                # if "收到" in content:
                #     for roomid, mentions in pending_mentions.items():
                #         for mentioned_id, mention_info in mentions.items():
                #             # 如果消息是被 @ 的人回复了 "收到"
                #             if mentioned_id == msg.sender:
                #                 # 更新状态为已回复
                #                 mentions[mentioned_id] = (
                #                     mention_info[0], True, mention_info[2], mention_info[3], reminder_count)  # 标记为已回复
                #                 print(f"@{mentioned_id} 回复了 '收到'，取消提醒")



    except Empty:
        continue
    except Exception as e:
        print(f'Error: {e}')
        continue